import datetime

import pymssql
from prettytable import *
from prettytable import SINGLE_BORDER, DOUBLE_BORDER


# 输入自动添加单引号
def input_quotes(s):
    return "'" + input(s) + "'"


# 去掉两边的单引号
def trim_quotes(s):
    return s[1:-1]


# 打印信息
def formatePrint(msg):
    print("【 " + msg + " 】")


# 执行SQL并输出
def table_run_sql_and_print(sql):
    cur.execute(sql)
    res = cur.fetchone()
    cur.execute(sql)
    if res:
        table = from_db_cursor(cur)
        table.set_style(DOUBLE_BORDER)
        print(table)
        return True
    else:
        formatePrint("查询结果为空")
        return False


if __name__ == '__main__':
    '''
        测试数据
        '''
    host = "127.0.0.1"
    port = "1433"
    user = "sa"
    password = "123456"

    # 连接数据库
    conn = pymssql.connect(host="127.0.0.1", database='master', user=user, password=password, port=port)
    cur = conn.cursor()
    cur.execute("use BOOKS_201900800290")
    sql = "select book_id 图书编号, name 图书名称, author 作者, publisher 出版社, publish_date 出版时间, price 价格, summary 简介, case when is_borrowed='TRUE' then N'是' else N'否' end as 是否被借阅 from book"
    cur.execute(sql)
    res = cur.fetchone()
    if res:
        print(res)
    print(cur.rowcount)
    cur.execute(sql)
    table = from_db_cursor(cur)
    table.set_style(PLAIN_COLUMNS)
    print(table)

    # name = input_quotes('请输入读者姓名：')
    # select_borrow_sql = '''select reader_name 借阅人, book.name 图书名称, borrow_date 借阅时间,
    #                         return_date 归还时间,
    #                         case when is_overdue='TRUE' then N'是' else N'否' end as 是否逾期,
    #                         overdue_money 逾期金额,
    #                         case when is_process='TRUE' then N'已处理' else N'未处理' end as 处理状态
    #                 from borrow,book where book.book_id = borrow.book_id and reader_name=%s''' % name
    # flag = table_run_sql_and_print(select_borrow_sql)
    # if flag:
    #     borrow_date = cur.fetchone()[2]
    #     book_id = input_quotes("请输入书籍编号：")
    #     return_date = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    #     select_reader_role_sql = '''select name 类型名称, borrow_num_limit 最大借阅数, borrow_date_limit 最大借阅天数 from role where name = (select role_name from reader_role where name = %s)''' % name
    #     cur.execute(select_reader_role_sql)
    #     borrow_date_limit = cur.fetchone()[2]
    #     print(datetime.datetime.strptime(return_date, '%Y-%m-%d %H:%M:%S') - datetime.datetime.strptime(borrow_date,'%Y-%m-%d %H:%M:%S') > datetime.timedelta(days=borrow_date_limit))
